import os
import shutil

import openpyxl
from openpyxl.utils import get_column_letter
from openpyxl.styles import Font
from openpyxl.comments import Comment
import datetime
import tools_pmc

wb_JH0022 = openpyxl.load_workbook(tools_pmc.lj_JH0022)
ws_JH0022 = wb_JH0022.worksheets[0]

wb = openpyxl.Workbook()
ws_xlzt = wb.worksheets[0]
ws_xlzt.title = "线缆货期"
wb.create_sheet(title="在制线缆", index=1)
ws_xlzz = wb.worksheets[1]
wb.create_sheet(title="相机配件货期", index=2)
ws_pjzt = wb.worksheets[2]

comment = Comment(
    "预交货日期大于需求日期，请注意！",
    'shen')
comment.width = 230
comment.height = 10


# noinspection PyShadowingNames
def 变更货期读取():
    dict_变更货期 = {}
    try:
        wb = openpyxl.load_workbook(
            "//192.168.70.101/06采购/001.采购共享-生产物料滚动需求计划（可编辑）/02.线缆需求及在途一览表2024/线缆备货--货期一览表" + tools_pmc.today_str1 + ".xlsx")
    except FileNotFoundError:
        wb = openpyxl.load_workbook(
            "//192.168.70.101/06采购/001.采购共享-生产物料滚动需求计划（可编辑）/02.线缆需求及在途一览表2024/线缆备货--货期一览表" + tools_pmc.yesterday_str1 + ".xlsx")
    ws = wb["线缆货期"]
    for i in range(2, ws.max_row + 1):
        dict_变更货期[ws.cell(i, 3).value] = ws.cell(i, 15).value
    try:
        wb = openpyxl.load_workbook(
            "//192.168.70.101/06采购/001.采购共享-生产物料滚动需求计划（可编辑）/02.线缆需求及在途一览表2024/配件备货--货期一览表" + tools_pmc.today_str1 + ".xlsx")
    except FileNotFoundError:
        wb = openpyxl.load_workbook(
            "//192.168.70.101/06采购/001.采购共享-生产物料滚动需求计划（可编辑）/02.线缆需求及在途一览表2024/配件备货--货期一览表" + tools_pmc.yesterday_str1 + ".xlsx")
    ws = wb["相机配件货期"]
    for i in range(2, ws.max_row + 1):
        dict_变更货期[ws.cell(i, 3).value] = ws.cell(i, 15).value
    return dict_变更货期


dict_变更货期 = 变更货期读取()


def remove(ws):
    aa = ws.max_row + 1
    for ii in range(1, ws_JH0022.max_column + 1):
        ws.cell(aa, ii).value = ws_JH0022.cell(i, ii).value


def main(ws):
    for ii in range(1, ws_JH0022.max_column + 1):
        ws.cell(1, ii).value = ws_JH0022.cell(1, ii).value
    ws.insert_cols(11)
    for ii in range(2, ws.max_row + 1):
        if ws.cell(ii, 2).value is not None:
            if "安全库存" in ws.cell(ii, 2).value:
                ws.cell(ii, 2).value = "安全库存"
            elif "销售紧急需求备料" in ws.cell(ii, 2).value:
                ws.cell(ii, 2).value = "紧急需求"
                ws.cell(ii, 2).fill = tools_pmc.fill_red
        ws.cell(ii, 9).value = ws.cell(ii, 9).value - ws.cell(ii, 10).value
        ws.cell(ii, 11).value = ws.cell(ii, 8).value - ws.cell(ii, 9).value - ws.cell(ii, 10).value
        for jj in range(12, 15):
            ws.cell(ii, jj).value = tools_pmc.detect_date(ws.cell(ii, jj).value, 1)
            ws.cell(ii, jj).number_format = 'yyyy/mm/dd'
        if ws.cell(ii, 9).value == 0 and ws.cell(ii, 14).value is not None:
            ws.cell(ii, 16).value = "在检"
            ws.cell(ii, 16).fill = tools_pmc.fill_green
        if ws.cell(ii, 16).value == "N":
            ws.cell(ii, 16).value = "审核中"
        elif ws.cell(ii, 17).value == "N":
            if ws.cell(ii, 8).value == 0:
                ws.cell(ii, 16).value = "审核中"
            else:
                ws.cell(ii, 16).value = "请购未结束"
        else:
            ws.cell(ii, 16).value = None
        if ws.cell(ii, 16).value is None and ws.cell(ii, 9).value == 0 and ws.cell(ii, 8).value != 0:
            ws.cell(ii, 16).value = "在检"
        if ws.cell(ii, 14).value is not None and ws.cell(ii, 14).value > ws.cell(ii, 13).value:
            ws.cell(ii, 14).fill = tools_pmc.fill_yellow
            ws.cell(ii, 13).fill = tools_pmc.fill_yellow
            ws.cell(ii, 14).comment = comment

    ws.delete_cols(17, 2)
    ws.insert_cols(15)
    for ii in range(2, ws.max_row + 1):
        ws.cell(ii, 15).value = dict_变更货期.get(ws.cell(ii, 3).value)
        ws.cell(ii, 15).number_format = 'yyyy/mm/dd'
        if ws.cell(ii, 15).value is not None:
            ws.cell(ii, 14).font = Font(strike=True)

        if ws.cell(ii, 14).value is not None:
            if ws.cell(ii, 15).value is None:
                if ws.cell(ii, 14).value.date() < datetime.date.today() and ws.cell(ii, 9).value != 0:
                    ws.cell(ii, 17).value = "已逾期"
                    ws.cell(ii, 17).fill = tools_pmc.fill_red

    ws.insert_cols(18)
    for ii in range(2, ws.max_row + 1):
        ws.cell(ii, 18).value = tools_pmc.vlookup(ws.cell(ii, 4).value, tools_pmc.table_array_wlxxb, 15)

    list11 = ["OA流水号", "需求来源", "请购单号", "品号", "品名", "规格", "请购数量", "采购数量", "在途数量",
              "在检数量",
              "入库数量",
              "请购日期", "需求日期", "预交货日期", "货期变更", "备注", "请购状态", "库存", "采购单号", "供应商",
              "采购单价"]
    list22 = [14, 10, 16.5, 14, 26, 26, 8, 8, 8, 8, 8, 12, 12, 12, 12, 20, 10, 8, 16.5, 10, 10]
    for ii in range(len(list11)):
        ws.cell(1, ii + 1).value = list11[ii]
        ws.cell(1, ii + 1).fill = tools_pmc.fill_blue
        ws.column_dimensions[get_column_letter(ii + 1)].width = list22[ii]
    ws.insert_rows(1)
    ws.cell(1, 1).value = "注：若货期不能满足需求，请及时反馈以便调整货期！"
    font_red = Font(color="FF0000")
    ws.cell(1, 1).font = font_red
    ws.cell(1, 12).value = "货期反馈时间：" + str(datetime.datetime.now())
    ws.freeze_panes = 'A3'
    ws.auto_filter.ref = "A2:T" + str(ws.max_row)


for i in range(2, ws_JH0022.max_row + 1):
    ws_JH0022.cell(i, 3).value = (ws_JH0022.cell(i, 3).value + "-" +
                                  ws_JH0022.cell(i, 4).value + "-" +
                                  ws_JH0022.cell(i, 5).value)

    if '3104-2024110026' in ws_JH0022.cell(i, 3).value:
        ws_JH0022.cell(i, 1).value = '202411180007'
    if '3104-2024090027' in ws_JH0022.cell(i, 3).value:
        ws_JH0022.cell(i, 2).value = '销售紧急需求备料（线缆、镜头）'
    if '3104-2025030023' in ws_JH0022.cell(i, 3).value:
        ws_JH0022.cell(i, 2).value = '销售紧急需求备料（线缆、镜头）'

    if ws_JH0022.cell(i, 20).value is not None:
        ws_JH0022.cell(i, 20).value = (ws_JH0022.cell(i, 20).value + "-" +
                                       ws_JH0022.cell(i, 21).value + "-" +
                                       ws_JH0022.cell(i, 22).value)
ws_JH0022.delete_cols(21, 2)
ws_JH0022.delete_cols(4, 2)

wb_data = openpyxl.load_workbook(tools_pmc.lj_data)
ws_pjsn = wb_data["配件品号"]
set_pjsn = set()
for i in range(2, ws_pjsn.max_row + 1):
    set_pjsn.add(ws_pjsn.cell(i, 1).value)


def remove_all():
    # if (ws_JH0022.cell(i, 2).value is not None
    #         and "3102" not in ws_JH0022.cell(i, 3).value
    #         and "预测" not in ws_JH0022.cell(i, 2).value):
    if ws_JH0022.cell(i, 4).value[0:5] == "10310" or ws_JH0022.cell(i, 4).value[0:5] == "10309":
        remove(ws_xlzt)
    elif int(ws_JH0022.cell(i, 4).value) in set_pjsn:
        remove(ws_pjzt)
    elif ws_JH0022.cell(i, 2).value is not None and (
            "安全库存" in ws_JH0022.cell(i, 2).value or "销售紧急需求备料" in ws_JH0022.cell(i, 2).value):
        remove(ws_pjzt)
        ws_pjsn.cell(ws_pjsn.max_row + 1, 1).value = int(ws_JH0022.cell(i, 4).value)


# else:
#     print(ws_JH0022.cell(i, 1).value,ws_JH0022.cell(i, 2).value,ws_JH0022.cell(i, 3).value)


for i in range(2, ws_JH0022.max_row + 1):
    if ((ws_JH0022.cell(i, 16).value == "Y" or ws_JH0022.cell(i, 16).value == "y")
            or (ws_JH0022.cell(i, 16).value != "Y" and ws_JH0022.cell(i, 16).value != "y")):
        remove_all()
wb_data.save(tools_pmc.lj_data)

main(ws_xlzt)
main(ws_pjzt)

wb_gdmx = openpyxl.load_workbook(tools_pmc.lj_gdmx)
ws_gdmx = wb_gdmx.worksheets[0]
ws_gdmx.insert_cols(3)
a = 2
for i in range(2, ws_gdmx.max_row + 1):
    ws_gdmx.cell(i, 3).value = ws_gdmx.cell(i, 1).value + "-" + ws_gdmx.cell(i, 2).value
    if (ws_gdmx.cell(i, 4).value[0:5] == "10309" or ws_gdmx.cell(i, 4).value[0:5] == "10310") and \
            ws_gdmx.cell(i, 14).value != "已完工" and \
            ws_gdmx.cell(i, 14).value != "指定完工" and \
            ws_gdmx.cell(i, 3).value != ws_xlzz.cell(a - 1, 1).value and\
            ws_gdmx.cell(i, 3).value != "5102-2021080002" and \
            ws_gdmx.cell(i, 3).value != "5102-2023040001" and \
            ws_gdmx.cell(i, 3).value != "5102-2024080028" and \
            ws_gdmx.cell(i, 3).value != "5102-2023090001":
        ws_xlzz.cell(a, 1).value = ws_gdmx.cell(i, 3).value
        ws_xlzz.cell(a, 2).value = ws_gdmx.cell(i, 4).value
        ws_xlzz.cell(a, 3).value = ws_gdmx.cell(i, 5).value
        ws_xlzz.cell(a, 4).value = ws_gdmx.cell(i, 6).value
        ws_xlzz.cell(a, 5).value = ws_gdmx.cell(i, 8).value
        ws_xlzz.cell(a, 6).value = ws_gdmx.cell(i, 10).value
        ws_xlzz.cell(a, 7).value = ws_gdmx.cell(i, 14).value
        if ws_xlzz.cell(a, 1).value[0:4] == "5102":
            ws_xlzz.cell(a, 8).value = "委外改制工单"
        a += 1

list1 = ["工单单号", "品号", "品名", "规格", "预计产量", "已生产数量", "当前状态", "备注"]
list2 = [16.5, 14, 26, 28, 10, 10, 10, 15]
for i in range(1, 9):
    ws_xlzz.cell(1, i).value = list1[i - 1]
    ws_xlzz.cell(1, i).fill = tools_pmc.fill_blue
    ws_xlzz.column_dimensions[get_column_letter(i)].width = list2[i - 1]
ws_xlzz.freeze_panes = 'A2'
ws_xlzz.auto_filter.ref = "A1:G" + str(ws_xlzz.max_row)

wb.save(tools_pmc.lj_xl_xlmx)


def 独立测试():
    ws_xlzt.delete_cols(21)
    ws_pjzt.delete_cols(21)
    wb.save(
        "//192.168.70.101/06采购/001.采购共享-生产物料滚动需求计划（可编辑）/02.线缆需求及在途一览表2024/线缆备货--货期一览表" + tools_pmc.today_str1 + ".xlsx")
    shutil.copy(
        "//192.168.70.101/06采购/001.采购共享-生产物料滚动需求计划（可编辑）/02.线缆需求及在途一览表2024/线缆备货--货期一览表" + tools_pmc.today_str1 + ".xlsx",
        "//192.168.70.101/06采购/001.采购共享-生产物料滚动需求计划（可编辑）/02.线缆需求及在途一览表2024/留存文件/线缆备货--货期一览表" + tools_pmc.today_str1 + ".xlsx")
    try:
        shutil.copy(
            "//192.168.70.101/06采购/001.采购共享-生产物料滚动需求计划（可编辑）/02.线缆需求及在途一览表2024/线缆备货--货期一览表" + tools_pmc.today_str1 + ".xlsx",
            r"\\192.168.70.101\09生产部\07.生产管理\记录表单-模板\来料检 IK-MC(QD)-MF(RF)-0XX")
    except PermissionError:
        pass
    ws_xlzt.delete_cols(20)
    ws_pjzt.delete_cols(20)
    wb.save("//192.168.70.101/01市场销售部/01市场销售公用/线缆备货--货期一览表" + tools_pmc.today_str1 + ".xlsx")

    try:
        os.remove(
            r"\\192.168.70.101\01市场销售部\01市场销售公用\线缆备货--货期一览表" + tools_pmc.yesterday_str1 + ".xlsx")
    except FileNotFoundError:
        print("文件不存在-销售-xlmx")
    except PermissionError:
        print("文件已打开-销售-xlmx")

    try:
        os.remove(
            r"\\192.168.70.101\06采购\001.采购共享-生产物料滚动需求计划（可编辑）\02.线缆需求及在途一览表2024\线缆备货--货期一览表" + tools_pmc.yesterday_str1 + ".xlsx")
    except FileNotFoundError:
        print("文件不存在-采购-xlmx")

    try:
        os.remove(
            r"\\192.168.70.101\09生产部\07.生产管理\记录表单-模板\来料检 IK-MC(QD)-MF(RF)-0XX\线缆备货--货期一览表" + tools_pmc.yesterday_str1 + ".xlsx")
    except FileNotFoundError:
        print("文件不存在-检验-xlmx")


独立测试()


def 采购表分解():
    try:
        os.remove(
            r"\\192.168.70.101\06采购\001.采购共享-生产物料滚动需求计划（可编辑）\02.线缆需求及在途一览表2024\配件备货--货期一览表" + tools_pmc.yesterday_str1 + ".xlsx")
    except FileNotFoundError:
        print("文件不存在-采购-配件表")

    # noinspection PyShadowingNames
    wb = openpyxl.load_workbook(
        "//192.168.70.101/06采购/001.采购共享-生产物料滚动需求计划（可编辑）/02.线缆需求及在途一览表2024/线缆备货--货期一览表" + tools_pmc.today_str1 + ".xlsx")
    wb.remove(wb.worksheets[0])
    wb.remove(wb.worksheets[0])
    wb.save(
        "//192.168.70.101/06采购/001.采购共享-生产物料滚动需求计划（可编辑）/02.线缆需求及在途一览表2024/配件备货--货期一览表" + tools_pmc.today_str1 + ".xlsx")
    shutil.copy(
        "//192.168.70.101/06采购/001.采购共享-生产物料滚动需求计划（可编辑）/02.线缆需求及在途一览表2024/配件备货--货期一览表" + tools_pmc.today_str1 + ".xlsx",
        "//192.168.70.101/06采购/001.采购共享-生产物料滚动需求计划（可编辑）/02.线缆需求及在途一览表2024/留存文件/配件备货--货期一览表" + tools_pmc.today_str1 + ".xlsx")


采购表分解()
